The present invention relates to database systems and, more particularly, to defining instead-of triggers over nested collection columns of views.
In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational database systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
Systems that implement the present invention are not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms xe2x80x9ctablexe2x80x9d, xe2x80x9crowxe2x80x9d and xe2x80x9ccolumnxe2x80x9d shall be used herein to refer respectively to the data container, record, and field.
A nested collection column of a table is a column that logically stores data items that belong to a collection data type. The collection of data items that logically reside within a row of a nested collection column is referred to as a nested collection. Nested collections may be ordered or unordered. Unordered nested collections are also referred to as nested tables. A table that has a nested collection column is referred to as the xe2x80x9cparent tablexe2x80x9d of the nested collections that logically reside within the nested collection column.
Tables with nested collection columns can be created, for example, using a SELECT query and a CAST-MULTISET operator. For example, consider a department-employee scenario, where the table xe2x80x9cempxe2x80x9d defines employee attributes and the table xe2x80x9cdeptxe2x80x9d defines department attributes. Assume that these tables are defined as follows:
Given the above-listed definitions for the emp and dept tables, the CAST-MULTISET operator may be used to create instances of the xe2x80x9cemp_list_txe2x80x9d type defined below, which represents a collection of the employee type xe2x80x9cemp_txe2x80x9d.
Using the data types defined above, a table dept_emp may be created that has a row for each department, where the row for a particular department has one column for the department number, one column for department name, and one nested collection column that includes the names of all the employees that belong to the department.
Individual items in the nested collections of dept_emp can be modified using the TABLE( ) clause. For example, the following statement could be used to insert a row for xe2x80x9cJohnxe2x80x9d into the nested collection associated with the dept_emp table row for department 10:
In this statement, the SELECT statement within the TABLE( ) clause identifies a specific row of the parent table (the row associated with department number 10). By identifying a specific row in the parent table, the clause effectively identifies a specific instance of the nested collection. Having identified a specific instance of the nested collection, the database server is able to determine how the corresponding base table (emp) must be updated.
In many cases, it may not be desirable for all users to have access to the data in the salary column of the emplist collections. To implement a security policy that selectively limits access to columns, a table that contains sensitive information, such as the dept_emp table, is usually not made available for direct user access. Rather, the data would typically be made available through the use of one or more views.
A view is a logical table. As logical tables, views may be queried by users as if they were a table. However, views actually present data that is extracted or derived from existing tables. For security reasons, views are often used to provide users with access to a subset of the data that is actually stored in the database system. Columns that contain sensitive information may be included in the views made available to one set of users, and not included in the views made available to another set of users.
A view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Columns and tables that are mapped to a view are referred to herein as base columns and base tables of the view, respectively.
Typically, the view definition is in the form of a database query. For example, a view dept_view that has a row for each department, where the row for a particular department has one column for the department number, one column for department name, and one nested collection column that includes the names of all the employees that belong to the department, may be defined by the following statement:
A view that may be created in response to this definition is illustrated in FIG. 1. Each row of the dept_view has three columns. Within each row, the emplist column has a nested collection. In dept_view 100, each nested collection is a virtual table that contains three columns. The structure of dept_view 100 is dictated by its view definition. The data that populates dept_view 100 is the data that resides in the based tables used to generate dept_view 100.
One type of view that is generally not updateable is a view that is used to present data in the form of a virtual table with a nested collection column. The base data for the nested collections, and for the other columns of the virtual parent table that contains the nested collections, may actually have been gathered by the database server from multiple base tables. This characteristic of views with nested collections generally renders the views capable of being directly updated. However, other types of views that are not directly dateable may be rendered updateable through the use of a type of trigger that is known as an xe2x80x9cinstead-ofxe2x80x9d trigger.
In a database management system, a trigger is an object that specifies a series of actions to be automatically performed when a specific event occurs. According to industry standards, Data Manipulation Language (DML) statements are the events that cause user-defined triggers to be activated (or xe2x80x9cfiredxe2x80x9d). For example, in a relational database, user-defined triggers may be designed to fire when a row of a database table or a table view is updated, inserted, or deleted. Accordingly, each user-defined trigger is typically associated with a single database table. That is, in a conventional database management system, the scope of the user-defined trigger is the table level of the database.
The series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, Calif.). In conformance with industry standards, these instructions must be able to access the data values of table columns corresponding to an affected row before the triggering DML statement was applied (the xe2x80x9cold valuesxe2x80x9d) and after the modification was applied (the xe2x80x9cnew valuesxe2x80x9d).
Since triggers are objects, database customers can define, remove, and store triggers associated with a database table, and the database management system keeps track of which triggers have been defined for which table by storing that information as metadata (information about data) associated with the table in a data dictionary for the database. Consequently, triggers enable database customers to implement additional functionality in their databases for such purposes as enforcement of business rules and security.
A trigger is defined by a database server in response to receiving a request to create a trigger. For example, consider the following SQL statement issued by a user:
Create Trigger TR_AFTER_EMPLOYEE After Update on FORMER_EMPLOYEE {ONE OR MORE BLOCKS OF CODE};
A statement issued for the purpose of defining a trigger is referred to as a trigger creation statement. In response to receiving the above trigger creation statement, the database server defines the trigger by adding to the data definition a trigger definition as specified by the SQL statement. In this example, the SQL statement specifies that a trigger named TR_AFTER_EMPLOYEE is to be invoked after the database server performs a direct update on relational table FORMER_EMPLOYEE.
The {ONE OR MORE BLOCKS OF CODE} represent instructions associated with TR_AFTER_EMPLOYEE. The instructions are executed whenever the trigger is invoked. The database server is able to execute instructions represented by block(s) of code. The block(s) of code may include, for example, code for updating an audit table, or for calling other procedures and functions.
An xe2x80x9cinstead-of triggerxe2x80x9d enables a database server to indirectly modify a database in response to requests to modify data through certain types of views that are not inherently modifiable. An instead-of trigger is a trigger that is invoked in response to a request to modify the database through a view associated with the instead-of-trigger, such as a request to perform an update, insert, or delete operation upon a view. When a database server receives a request to modify a view, and an instead-of trigger is defined for the view, the database server invokes the instead-of trigger and foregoes any attempt to perform any direct modification operation itself.
Using conventional instead-of triggers, it is possible to perform DML operations on the data items within the virtual parent table of a view that contains a nested collection column. For example, an instead-of trigger may be defined over dept_view to insert, delete, or modify rows of dept_view. Such an instead-of trigger would fire once for each row of dept_view that a DML statement attempts to modify. Because each nested collection in the emplist column of dept_view is a data item within the virtual parent table, an instead-of trigger defined over dept_view may be used to manipulate the nested collection as an atomic data item.
Unfortunately, such an instead-of trigger cannot efficiently handle DML operations that target individual elements of nested collections. For example, if an update merely required the addition of an element to the collection item of a row, that operation could be handled by a conventional instead-of trigger designed to delete the entire collection object of the row and re-insert a new collection object that includes all of the original elements plus the element to be inserted.
Unfortunately, using an instead-of trigger to delete and recreate entire collection items incurs an amount processing overhead that can be hugely disproportional to the actual act being performed. For example, the insertion of a single element in a million-element collection item would cause the entire recreation of the million-element collection item. Unfortunately, conventional database systems do not provide any mechanism by which instead-of triggers can directly access and manipulate the individual rows within nested collections that are defined by views.
Based on the foregoing, it is desirable to provide a technique by which DML operations may be performed on individual data items within nested collections that logically reside within nested collection columns of views.
Techniques are provided for updating data that logically resides in one or more nested collections in a nested collection column of a view. According to one technique, a Data Manipulation Language (DML) statement that operates on individual elements within the one or more nested collections is detected. Instead of executing the DML statement, trigger code is executed once for each individual element, within the one or more nested collections, that is targeted by the DML statement. In addition, the trigger code may include one or more references to parent values. For each nested collection selected by the DML statement, the following steps are performed when the trigger code includes references to parent values: determining the row, within the view, in which the nested collection resides and, prior to executing the trigger code for the nested collection, binding the one or more references to one or more corresponding values that logically reside in the row.